/* Copyright (C) 2010  Romolo Camplani, Marco Fortunato, Marco Marelli, Guido Rota, Fabio A. Schreiber et al.
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 */

package org.dei.perla.sys.persistence;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;


import org.dei.perla.parser.expressions.LogicValue;
import org.dei.perla.sys.core.DefaultAgent;
import org.dei.perla.sys.core.PerlaConfigurationException;
import org.dei.perla.sys.core.PerlaRuntime;
import org.dei.perla.sys.query.llqruntime.datastructures.Buffer;
import org.dei.perla.sys.query.llqruntime.datastructures.FailedDeleteBufferException;
import org.dei.perla.sys.query.llqruntime.datastructures.OutOfBoundsBufferException;
import org.dei.perla.sys.query.llqruntime.datastructures.QueryFieldStructure;
import org.dei.perla.sys.query.llqruntime.datastructures.Record;

/**
 * Fornisce i metodi neccessari per rendere persistenti i record presenti nella 
 * stuttura dati Buffer. La persistenza viene realizzata sfruttando come DB MySql.
 * @author Luigi Rinaldi & Matteo Rovero
 */
public  abstract class DataPersistent extends DefaultAgent<Object, Object> {

	protected Buffer buff;
	protected String nameTable;
	
	private Connection con = null;
	private PreparedStatement ps = null;
	private Statement stmt = null;
	private String sqlPS;
	private String URL;
	private String Auth;
	private  String Driver;

	
	
	/**
	 * Inizializza il Buffer e il nome della tabella sql in cui verranno salvati i
	 * record.
	 * @param buff struttura di tipo Buffer dal cui recuperare i record da salvere
	 * nel DB. 
	 * @param nameOfTable tabella MySql che conterra' i record del buff.
	 * @throws NullBufferException eccezione sollevata nel caso in cui il Buffer passato sia null.
	 * @throws NotValidNameException eccezione sollevata nel caso in cui il nome della Tabella risulta 
	 * gia' utilizzato nel DB.
	 * @throws PerlaConfigurationException 
	 */
	protected DataPersistent(Buffer buff, String nameTable) throws NullBufferException, NotValidNameException, PerlaConfigurationException{
		super(DataPersistent.class.getCanonicalName() + nameTable);
		
		PerlaRuntime runtime = PerlaRuntime.getDefaultRuntime();
		URL = runtime.getProperty("PERLA_DB");
		Auth = runtime.getProperty("PERLA_DB_AUTH");
		if(URL == null || Auth == null)
			throw new PerlaConfigurationException();
		if (buff == null) throw new NullBufferException("Invalid Buffer. Buffer can't be null");
		//for(String s : nameTables()){
		//	if (s.toLowerCase().equals(nameTable.toLowerCase())) throw new NotValidNameException("Invalid table's name. Table name already exsist");		
		//}

		this.buff = buff;
		this.nameTable = nameTable;
	}

	
	
	/**
	 * Realizza una connessione con il DB e inizializza un oggetto PreparedStatement con una stringa
	 * contenente la struttura del record da salvare. L'oggetto PreparedStatement servir� successivamente
	 * per memorizzare i Record in batch.
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	private void openConnectioToDB() throws SQLException{
		
        con = DriverManager.getConnection(URL + Auth); 
        //TODO LOG
        System.out.println ("Connection OK!"); 
        
        con.setAutoCommit(true);
        ps = con.prepareStatement(sqlPS);
        stmt = con.createStatement();
    }

	/**
	 * Chiude la connessione con il DB e rilascia le risorse in maniera corretta.
	 * @throws SQLException
	 */
	private void closeConnectionToDB() throws SQLException{
		
		if(ps!=null){
            ps.close();
        }
		if(stmt!=null){
            stmt.close();
        }
        if(con!=null){
           con.close();
        }
	}
	
	
	
	
	/**
	 * Aggiunge un recod al batch sfruttando il prepared statement creato con il costruttore 
	 * della classe.
	 * @param rec oggetto di tipo Record da aggiungere al batch.
	 * @param IDrecord � un numero intero positivo univoco che identifica ogni record di una 
	 * tabella sql.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	private void addRecordBatch(Record rec, int IDrecord) throws SQLException, ClassNotFoundException{
		
		HashMap<String,QueryFieldStructure> map = rec.getRecordStructure(); 
		String[] str = new String[rec.length()];
		for(String key: map.keySet() ){
			str[map.get(key).getFieldIndex()] = map.get(key).getFieldName();
		}
		
		
					
		ps.setNull(1,java.sql.Types.INTEGER );
		for(int i=0; i<rec.length(); i++){
			String s = rec.getField(i).getClass().getSimpleName();
			if(s.equals("ConstantInteger") ){
				ps.setInt(i+2, ((Integer)rec.getField(i).getSqlValue()).intValue());
			}
			if(s.equals("ConstantFloat")){
				ps.setDouble(i+2, ((Double)rec.getField(i).getSqlValue()).doubleValue());
			}
			if(s.equals("ConstantID")){
				ps.setString(i+2, ((String)rec.getField(i).getSqlValue()));
			}
			if(s.equals("ConstantLogic")){
				ps.setByte(i+2,(byte) ((LogicValue)rec.getField(i).getSqlValue()).getIntValue());
			}
			if(s.equals("ConstantNull")){
				ps.setNull(i+2,java.sql.Types.NULL);
			}
			if(s.equals("ConstantString")){
				ps.setString(i+2, ((String)rec.getField(i).getSqlValue()));
			}
			if(s.equals("ConstantTimestamp")){
				ps.setTimestamp(i+2,(Timestamp)rec.getField(i).getSqlValue());
			}
			if(s.equals("ConstantVectorInteger")){
			
				ps.setInt(i+2,IDrecord);
				Integer[] v = (Integer[]) ((ArraySql)rec.getField(i).getSqlValue()).getArray();
				String nameVecTable = nameTable+str[i];
				
				for(int k=0; k<v.length; k++){
					stmt.addBatch("INSERT INTO " +nameVecTable+ " VALUES (" +IDrecord+","+k+ ","+v[k]+ ")");
				}
			}
			if(s.equals("ConstantVectorFloat")){
		
				ps.setInt(i+2,IDrecord);
				Double[] v = (Double[]) ((ArraySql)rec.getField(i).getSqlValue()).getArray();
				String nameVecTable = nameTable+str[i];
			
				for(int k=0; k<v.length; k++){
					stmt.addBatch("INSERT INTO " +nameVecTable+ " VALUES (" +IDrecord+"," +k+ "," +v[k]+ ")");
				}
			}
			if(s.equals("ConstantBlob") || s.equals("ConstantFile")){
				ps.setBlob(i+2, ((BlobSql)rec.getField(i).getSqlValue()));
			}
		}
		ps.addBatch();		
	}
	

	/**
	 * Fornisce un numero intero che identifica il record con il massimo ID inserito 
	 * nella tabella sql. La tabella sql considerata � quella con il nome memorizzato
	 * nell'attributo nameTable opportunamente inizializzato con il costuttore della classe. 
	 * @return un intero positivo che identifica il record con id massamo memorizzato nella 
	 * tabella sql considerata.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private int getLastID() throws ClassNotFoundException, SQLException{
		Statement st = null;
		ResultSet rs = null;
		Class.forName(Driver);
        con = DriverManager.getConnection(URL + Auth); 
        st = con.createStatement();
        
		rs = st.executeQuery("SELECT MAX(IDrecord) FROM " +this.nameTable);
		rs.next();
		System.out.println("ID MASSIMO = "+rs.getInt(1));
		return rs.getInt(1);
		
	}
	
	
	
	/**
	 * Recupera i nomi delle tabelle presenti nel database.
	 * @return un array di stringhe contenente i nomi delle tabelle presenti nel DB .
	 */
	protected final ArrayList<String> nameTables(){
        int tablesCounter=0;
        ArrayList<String> tableName = new ArrayList<String>();  
        try
        {
        	try {
				Class.forName(Driver);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
            con = DriverManager.getConnection(URL + Auth); 
            System.out.println ("Connessione OK!");
        	
            DatabaseMetaData dbmd = con.getMetaData();
            ResultSet rsTables = dbmd.getTables(null,Auth.toUpperCase(),null,null);
            while(rsTables.next())
            {
                if(rsTables.getString(4).equals("TABLE"))
                {
                    ++tablesCounter;
                }
            }
                      
            int counter=0;
            rsTables = dbmd.getTables(null,Auth.toUpperCase(), null, null); 
            while(rsTables.next())
            { 
                String strTable = rsTables.getString(3);
                boolean debug=false;
                for (int i=0; i<strTable.length(); i++)
                {
                   	if(strTable.charAt(i)=='$')
	                {
	                		debug=true;
	                }
                }
                if(!debug)
                {
                   tableName.add(strTable);
	                ++counter;
                }
	            else
	                --tablesCounter;
            }  
            //Chiusura delle risorse 
            con.close();
        }
        catch(SQLException ex1){}
        return tableName;  
    }
	
	
	
    /**
	 * Crea una tabella avente come nome la stringa contenuta nell'attributo nameTable, inizializzato dal 
	 * costruttore delle classe. 
	 * Inoltre per ogni campo, di tipo vettore, presente nella tabella, viene creta una nuova tablella 
	 * avente come nome la concatenazione delle stringhe nameTable e nome del campo vettore.
	 * I campi della nuova tabella contenete gli elementi del vettore sono i seguenti: 
	 * Vector, VectorCell, VectorElement,
	 * dove Vector � referenziato con una chiave esterna con il campo vettore in questione. Vector e VectorCell 
	 * formano una chiave per la tabella referenziata.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	protected final void createTable() throws ClassNotFoundException, SQLException{
		Record rec = buff.getBuffer().get(0);
		String sqlCreateTable = "CREATE TABLE IF NOT EXISTS " +nameTable+ " (IDrecord INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, " ;  
		sqlPS = "INSERT INTO "+nameTable+" VALUE (?";
		HashMap<String,QueryFieldStructure> map = rec.getRecordStructure(); 
		String[] str = new String[rec.length()];
		for(String key: map.keySet() ){
			str[map.get(key).getFieldIndex()] = map.get(key).getFieldName();
		}
		ArrayList<String> vecIntegerTableName = new ArrayList<String>();
		ArrayList<String> vecFloatTableName = new ArrayList<String>();
		
		for(int i=0; i<rec.length(); i++){
			String s = rec.getField(i).getClass().getSimpleName();
			sqlPS = sqlPS +",?";
			
			if(s.equals("ConstantInteger") ){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" INTEGER, ";
			}
			if(s.equals("ConstantFloat")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" DOUBLE, ";
			}
			if(s.equals("ConstantID")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" VARCHAR(100), ";
			}
			if(s.equals("ConstantLogic")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" TINYINT(1), ";
			}
			if(s.equals("ConstantNull")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" INTEGER, ";
			}
			if(s.equals("ConstantString")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" VARCHAR(1000), ";
			}
			if(s.equals("ConstantTimestamp")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" TIMESTAMP, ";
			}
			if(s.equals("ConstantVectorInteger")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" INTEGER  UNSIGNED NOT NULL UNIQUE, ";
				vecIntegerTableName.add(str[i]);
			}
			if(s.equals("ConstantVectorFloat")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" INTEGER  UNSIGNED NOT NULL UNIQUE, ";
				vecFloatTableName.add(str[i]);
			}
			if(s.equals("ConstantBlob")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" BLOB, ";
			}
			if(s.equals("ConstantFile")){
				sqlCreateTable=sqlCreateTable+" "+str[i]+" LONGBLOB, ";
			}
		}
		
		sqlCreateTable = sqlCreateTable +" PRIMARY KEY (IDrecord)";
		sqlPS = sqlPS +")";
		sqlCreateTable=sqlCreateTable+")";
		
		System.out.println(sqlPS);
		System.out.println(sqlCreateTable);
		
		
		
        Class.forName(Driver);
        con = DriverManager.getConnection(URL + Auth); 
        stmt = con.createStatement();
        stmt.addBatch(sqlCreateTable);
        
       
		for(int i=0; i< vecIntegerTableName.size(); i++){
			 String sql = "CREATE TABLE IF NOT EXISTS " +nameTable+vecIntegerTableName.get(i)+ 
			 " (Vector INTEGER UNSIGNED NOT NULL, VectorCell INTEGER UNSIGNED NOT NULL, VectorElement INTEGER, PRIMARY KEY (Vector, VectorCell), FOREIGN KEY (Vector) REFERENCES " +nameTable+ " (" +vecIntegerTableName.get(i)+ ") ON DELETE CASCADE ON UPDATE CASCADE )";
			 stmt.addBatch(sql);
			 System.out.println(sql);
		}
		
		
		for(int i=0; i< vecFloatTableName.size(); i++){
			 String sql= "CREATE TABLE IF NOT EXISTS " +nameTable+vecFloatTableName.get(i)+ 
			 " (Vector INTEGER UNSIGNED NOT NULL, VectorCell INTEGER UNSIGNED NOT NULL, VectorElement DOUBLE, PRIMARY KEY (Vector, VectorCell), FOREIGN KEY (Vector) REFERENCES " +nameTable+ " (" +vecFloatTableName.get(i)+ ") ON DELETE CASCADE ON UPDATE CASCADE )";
			 stmt.addBatch(sql);
			 System.out.println(sql);
		}
		
		stmt.executeBatch();

    
        if(stmt!=null){
            stmt.close();
        }
        if(con!=null){
           con.close();
        }
	}
	
	
	/**
	 * Salva i record contenuti nell'oggetto Buffer, nella tabella del dB precedentemente creata,
	 * e progressivamente li cancella dal Buffer.
	 * @throws OutOfBoundsBufferException 
	 */
	protected final void saveRecords(int size) throws OutOfBoundsBufferException{
		ArrayList<Record> recList = null;
		
		try {
			recList = buff.emptyOutBuffer(size);
		} catch (FailedDeleteBufferException e1) {
			e1.printStackTrace();
		}
		if (recList.isEmpty())
			return;
		try {
			openConnectioToDB();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
		int id =0;
		try {
			id = getLastID();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		for(Record r : recList){
			id++;
			try {
				System.out.println("Record aggiunto al batch: \n" + r.toString());
				addRecordBatch(r,id);
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}	
		}
		try {
			ps.executeBatch();
			stmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			closeConnectionToDB();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
